package com.ipartek.formacion.gestformwww.modelo.clases;

import com.ipartek.formacion.gestformwww.modelo.beans.*;
import com.ipartek.formacion.gestformwww.modelo.beans.excepciones.GestorFormacionExcepcion;
import com.ipartek.formacion.gestformwww.modelo.interfaces.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.ResourceBundle;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * @author Sara y Aitor
 */
public class SQLServerAlumnoDAO implements IAlumnoDAO {

    private static ResourceBundle rb;

    public SQLServerAlumnoDAO() {
        rb = ResourceBundle.getBundle("com.ipartek.formacion.gestformwww.controlador.excepciones");
    }

    /**
     * Método que asigna alumnos a un curso.
     * @author Sara y Alvaro
     * @param ArrayList<Alumnos>: Lista de alumnos.
     * @param idCurso: Identificador del curso.
     */
    public void asignarAlumnos(ArrayList alumnos, int idCurso) throws GestorFormacionExcepcion {
        Connection conexion = null;

        try {
            conexion = SQLServerDAOFactory.conecta();
        } catch (SQLException ex) {
            Logger.getLogger(SQLServerAlumnoDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-conexion"));
        }

        try {
            PreparedStatement stmt = null;
            stmt = conexion.prepareStatement("INSERT INTO alumcurso (AluId, CurId) VALUES(?, ?)");
            for (Integer idAlumno : (ArrayList<Integer>) alumnos) {
                stmt.setInt(1, idAlumno);
                stmt.setInt(2, idCurso);
                stmt.executeUpdate();
            }
            stmt.close();
        } catch (SQLException ex) {
            if (ex.getErrorCode() == 2627) {
                Logger.getLogger(SQLServerAlumnoDAO.class.getName()).log(Level.SEVERE, null, ex);
                throw new GestorFormacionExcepcion(rb.getString("ya-existe"));
            } else {
                Logger.getLogger(SQLServerAlumnoDAO.class.getName()).log(Level.SEVERE, null, ex);
                throw new GestorFormacionExcepcion(rb.getString("no-sql"));
            }
        } finally {
            SQLServerDAOFactory.desconecta(conexion);
        }
    }

    /**
     * Método que calcula el numero de matriculas de un alumno determinado.
     * @author Sara y Aitor
     * @param idAlumno: Identificador del alumno
     * @return Devuelve el numero de matriculas que tiene ese alumno.
     */
    public int cuentaMatricula(int idAlumno) throws GestorFormacionExcepcion {
        int mat = 0;
        Connection conexion = null;

        try {
            conexion = SQLServerDAOFactory.conecta();
        } catch (SQLException ex) {
            Logger.getLogger(SQLServerAlumnoDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-conexion"));
        }

        try {
            PreparedStatement stmt = conexion.prepareStatement("SELECT Count(AluId) AS matriculas FROM alumcurso WHERE AluId = ?");
            stmt.setInt(1, idAlumno);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                mat = rs.getInt("matriculas");
            }
            rs.close();
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(SQLServerAlumnoDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-sql"));
        } finally {
            SQLServerDAOFactory.desconecta(conexion);
            return mat;
        }
    }

    /**
     * Método que calcula el numero de matriculas que tienen los alumnos.
     * @author Sara y Alvaro
     * @param alumnos: Lista de alumnos
     * @return Devuelve una lista con el numero de matriculas que tiene cada alumno.
     */
    public ArrayList cuentaMatricula(ArrayList<Alumno> alumnos) throws GestorFormacionExcepcion {
        ArrayList mat = new ArrayList();
        Connection conexion = null;

        try {
            conexion = SQLServerDAOFactory.conecta();
        } catch (SQLException ex) {
            Logger.getLogger(SQLServerAlumnoDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-conexion"));
        }

        try {
            PreparedStatement stmt = null;
            ResultSet rs = null;
            for (int i = 0; i < alumnos.size(); i++) {
                stmt = conexion.prepareStatement("SELECT Count(AluId) AS matriculas FROM alumcurso WHERE AluId = ?");
                stmt.setInt(1, alumnos.get(i).getAluId());
                rs = stmt.executeQuery();
                if (rs.next()) {
                    mat.add(rs.getInt("matriculas"));
                }
            }
            rs.close();
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(SQLServerAlumnoDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-sql"));
        } finally {
            SQLServerDAOFactory.desconecta(conexion);
            return mat;
        }
    }

    /**
     * Crea un alumno nuevo en la base de datos, comprobando que ya exista anteriormente.
     * @author Sara
     * @param alumno: Contiene los datos del alumno.
     * @return Devuelve el identificador del alumno. En el caso de que no se cree, devuelve -1.
     */
    public int crearAlumno(Alumno alumno) throws GestorFormacionExcepcion {
        Connection con = null;
        SentenciaNula stmt = null;
        ResultSet rs = null;
        int id = -1;

        try {
            con = SQLServerDAOFactory.conecta();
        } catch (SQLException ex) {
            Logger.getLogger(SQLServerAlumnoDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-conexion"));
        }

        try {
            PreparedStatement sql = con.prepareStatement("SELECT AluId, AluNIF, AluNSS, AluTlf1, AluTlf2, AluMail, AluDir, AluLocalidad, AluFechNac, AluNom, AluApe1, AluApe2, AluObserv, EmpId, ProId FROM Alumno WHERE UPPER(AluNIF)=?");
            sql.setString(1, alumno.getAluNIF().toUpperCase());
            rs=sql.executeQuery();

            if(!rs.next()){
                stmt = new SentenciaNula(con, "INSERT INTO Alumno ( AluNIF, AluNSS, AluTlf1, AluTlf2, AluMail, AluDir, AluLocalidad, AluFechNac, AluNom, AluApe1, AluApe2, AluObserv, EmpId, ProId)VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
                stmt.setString(1, alumno.getAluNIF());
                stmt.setString(2, alumno.getAluNSS());
                stmt.setString(3, alumno.getAluTlf1());
                stmt.setString(4, alumno.getAluTlf2());
                stmt.setString(5, alumno.getAluMail());
                stmt.setString(6, alumno.getAluDir());
                stmt.setString(7, alumno.getAluLocalidad());
                stmt.setTimestamp(8, alumno.getAluFechNac());
                stmt.setString(9, alumno.getAluNom());
                stmt.setString(10, alumno.getAluApe1());
                stmt.setString(11, alumno.getAluApe2());
                stmt.setString(12, alumno.getAluObserv());
                stmt.setInt(13, alumno.getEmpId());
                stmt.setInt(14, alumno.getProId());
                stmt.executeUpdate();
                rs = stmt.getGeneratedKeys();
                if (rs.next()) {
                    id = rs.getInt(1);
                }
            }else{
                GestorFormacionExcepcion ex = new GestorFormacionExcepcion(rb.getString("existe-nif"));
                alumno=this.instanciarAlumno(rs);
                ex.setObjetoExcepcion(alumno);
                throw ex;
            }
            sql.close();
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(SQLServerAlumnoDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-sql"));

        } finally {
            SQLServerDAOFactory.desconecta(con);
        }

        return id;
    }

    /**
     * Elimina un alumno de la base de datos, incluyendo los datos relacionados con ese alumno en la tabla ALUMCURSO.
     * @author Sara
     * @param idAlumno: Identificador del alumno.
     * @param idCurso: Identificador del curso.
     */
    public void eliminarAlumno(int idAlumno, int idCurso) throws GestorFormacionExcepcion {
        Connection con = null;

        try {
            con = SQLServerDAOFactory.conecta();
        } catch (SQLException ex) {
            throw new GestorFormacionExcepcion(rb.getString("no-conexion"));
        }
        try {
            con.setAutoCommit(false);
            PreparedStatement stmt;
            //BORRAMOS la informacion del alumno del curso
            stmt = con.prepareStatement("DELETE FROM AlumCurso WHERE AluId=" + idAlumno + "AND CurId=" + idCurso);
            stmt.executeUpdate();
            //borrar las calificaciones del alumnos en el curso
            stmt = con.prepareStatement("DELETE FROM Calificacion WHERE AluId=" + idAlumno + "AND CurId=" + idCurso);
            stmt.executeUpdate();
            //borrar las asistencias del alumno en el curso
            stmt = con.prepareStatement("DELETE FROM Asistencia WHERE AluId=" + idAlumno + "AND CurId=" + idCurso);
            stmt.executeUpdate();
            // Buscamos si ese alumno esta en más cursos
            stmt = con.prepareStatement("SELECT Practicas, ValFinal, AluId, CurId, Terminado, FchUltiCla, Reserva, Orden, Avisos, Observaciones FROM alumcurso WHERE AluId=" + idAlumno);
            ResultSet rs = stmt.executeQuery();
            if (!rs.next()) {
                //BORRAMOS El alumno si no está en más cursos.
                stmt = con.prepareStatement("DELETE FROM Alumno WHERE AluId=" + idAlumno);
                stmt.executeUpdate();
                //BORRAMOS SMSEnvios_Alumnos
                stmt = con.prepareStatement("DELETE FROM SMSEnvios_Alumnos WHERE AluId=" + idAlumno);
                stmt.executeUpdate();
            }
            con.commit();
            stmt.close();
        } catch (SQLException ex) {
            try {
                con.rollback();
            } catch (SQLException ex1) {
                Logger.getLogger(SQLServerAlumnoDAO.class.getName()).log(Level.SEVERE, null, ex1);
                throw new GestorFormacionExcepcion(rb.getString("no-sql"));
            }
            throw new GestorFormacionExcepcion(rb.getString("no-sql"));
        } finally {
            SQLServerDAOFactory.desconecta(con);
        }
    }

    /**
     * Metodo que crea un objeto alumno a partir de los resultados obtenidos de la consulta a la base de datos.
     * @author
     * @param rs: ResultSet obtenido de una consulta SQL.
     * @return Un alumno
     */
    public Alumno instanciarAlumno(ResultSet rs) throws SQLException {
        Alumno alumno = new Alumno();

        alumno.setAluId(rs.getInt("AluId"));
        alumno.setAluNom(rs.getString("AluNom"));
        alumno.setAluNIF(rs.getString("AluNIF"));
        alumno.setAluNSS(rs.getString("AluNSS"));
        alumno.setAluTlf1(rs.getString("AluTlf1"));
        alumno.setAluTlf2(rs.getString("AluTlf2"));
        alumno.setAluMail(rs.getString("AluMail"));
        alumno.setAluDir(rs.getString("AluDir"));
        alumno.setAluLocalidad(rs.getString("AluLocalidad"));
        alumno.setAluFechNac(rs.getTimestamp("AluFechNac")); //fecha al reves
        alumno.setAluApe1(rs.getString("AluApe1"));
        alumno.setAluApe2(rs.getString("AluApe2"));
        alumno.setAluObserv(rs.getString("AluObserv"));
        alumno.setEmpId(rs.getInt("EmpId"));
        alumno.setProId(rs.getInt("ProId"));

        return alumno;
    }

    /**
     * Metodo qur crea un objeto alumno a partir de los resultados obtenidos de la consulta a la base de datos.
     * @author
     * @param rs: ResultSet obtenido de una consulta SQL.
     * @param idCurso: Identificador del curso.
     * @return Un alumno
     */
    public Alumno instanciarAlumno(ResultSet rs, int idCurso) throws SQLException, GestorFormacionExcepcion {
        Alumno alumno = new Alumno();
        DAOFactory factoria = DAOFactory.getDAOFactory(DAOFactory.SQLSERVER);
        IAsistenciaDAO asistenciaDAO = factoria.getIAsistenciaDAO();
        ICalificacionDAO calificacionDAO = factoria.getICalificacionDAO();
        IAlumCursoDAO alumCursoDAO = factoria.getIAlumCursoDAO();

        alumno.setAluId(rs.getInt("AluId"));
        alumno.setAluNom(rs.getString("AluNom"));
        alumno.setAluNIF(rs.getString("AluNIF"));
        alumno.setAluNSS(rs.getString("AluNSS"));
        alumno.setAluTlf1(rs.getString("AluTlf1"));
        alumno.setAluTlf2(rs.getString("AluTlf2"));
        alumno.setAluMail(rs.getString("AluMail"));
        alumno.setAluDir(rs.getString("AluDir"));
        alumno.setAluLocalidad(rs.getString("AluLocalidad"));
        alumno.setAluFechNac(rs.getTimestamp("AluFechNac")); //fecha al reves
        alumno.setAluApe1(rs.getString("AluApe1"));
        alumno.setAluApe2(rs.getString("AluApe2"));
        alumno.setAluObserv(rs.getString("AluObserv"));
        alumno.setEmpId(rs.getInt("EmpId"));
        alumno.setProId(rs.getInt("ProId"));

        try {
            alumno.setAsistencia(asistenciaDAO.buscarAsistencia(rs.getInt("AluId"), idCurso));
            alumno.setCalificaciones(calificacionDAO.buscarCalificacion(rs.getInt("AluId"), idCurso));
            alumno.setAlumnCurso(alumCursoDAO.buscarAlumCurso(rs.getInt("AluId"), idCurso));
        } catch (GestorFormacionExcepcion ex) {
            Logger.getLogger(SQLServerAlumnoDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-sql"));
        }

        return alumno;
    }

    /**
     * Método que lista todos los alumnos.
     * @author
     * @param filtro: Filtro que hay que aplicar a la consulta.
     * @return Devuelve una lista con todos los alumnos y sus datos.
     */
    public ArrayList<Alumno> listarAlumnos(FiltroAlumno filtro) throws GestorFormacionExcepcion {
        ArrayList<Alumno> alumnos = new ArrayList<Alumno>();
        Alumno alumno;
        Connection conexion = null;
        String sql = null;

        /*if (filtro.isNocurso()) {
            sql = filtro.getSQLPag();
        } else {*/
            sql = filtro.getSQL();
        //}

        try {
            conexion = SQLServerDAOFactory.conecta();
        } catch (SQLException ex) {
            Logger.getLogger(SQLServerAlumnoDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-conexion"));
        }

        try {
            PreparedStatement stmt = conexion.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                if(filtro.isNocurso()){
                    if((rs.getRow()>=filtro.getPrimerElemento()&& rs.getRow()<=filtro.getUltimoElemento())){
                    alumno = instanciarAlumno(rs, filtro.getCurId());
                    alumnos.add(alumno);
                    }
                }else{
                    alumno = instanciarAlumno(rs, filtro.getCurId());
                    alumnos.add(alumno);
                }
            }
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(SQLServerAlumnoDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-sql"));
        } finally {
            SQLServerDAOFactory.desconecta(conexion);
        }

        return alumnos;
    }

    /**
     * Metodo que cuenta el número de alumnos devueltos al aplicar el filtro establecido.* Autor: Xarles y Borja
     * Autor: Xarles y Borja
     * @param filtro: Contiene los datos de filtrado del alumno.
     * @return Devuelve el número de registros obtenidos.
     */
    public int contarAlumnos(FiltroAlumno filtro) throws GestorFormacionExcepcion {
        int registros = 0;
        Connection conexion = null;
        String sql = filtro.getSQL();

        try {
            conexion = SQLServerDAOFactory.conecta();
        } catch (SQLException ex) {
            Logger.getLogger(SQLServerAlumnoDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-conexion"));
        }

        try {
            PreparedStatement stmt = conexion.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = stmt.executeQuery();

            if (rs.last()) {
                registros = rs.getRow();
            }
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(SQLServerAlumnoDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-sql"));
        } finally {
            SQLServerDAOFactory.desconecta(conexion);
        }

        return registros;
    }

    /**
     * Metodo que visualiza los datos de un alumno en la base de datos.
     * Autor: Sara y Alvaro
     * @param idAlumno: Contiene el identficador del alumno.
     * @param idCurso: Contiene el identificador del curso
     * @return Devuelve un alumno.
     */
    public Alumno visualizarAlumno(int idAlumno, int idCurso) throws GestorFormacionExcepcion {
        Alumno alumno = new Alumno();
        Connection conexion = null;

        try {
            conexion = SQLServerDAOFactory.conecta();
        } catch (SQLException ex) {
            Logger.getLogger(SQLServerAlumnoDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-conexion"));
        }

        try {
            PreparedStatement stmt = conexion.prepareStatement("SELECT AluId, AluNIF, AluNSS, AluTlf1, AluTlf2, AluMail, AluDir, AluLocalidad, AluFechNac, AluNom, AluApe1, AluApe2, AluObserv, EmpId, ProId FROM alumno WHERE AluId=?");
            stmt.setInt(1, idAlumno);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                alumno = instanciarAlumno(rs, idCurso);
            }
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(SQLServerAlumnoDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-sql"));
        } catch (GestorFormacionExcepcion ex) {
            Logger.getLogger(SQLServerAlumnoDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString(ex.getMessage()));
        } finally {
            SQLServerDAOFactory.desconecta(conexion);
        }
        
        return alumno;
    }

    /**
     * Obtiene la lista de SMS-envío.
     * Autor: Xarles y Borja
     * @return Devuelve un ArrayList que contiene todos los SMS.
     */
    public ArrayList<SMSEnvio> listarSMS() {
        ArrayList<SMSEnvio> listaSMS = new ArrayList<SMSEnvio>();
        SMSEnvio sms;
        Connection conexion = null;

        String sql = ("Select IDCandidato, SMSMenId, IDConvocatoria, IDRespuesta, FechaHora,Movil, Mensaje from SMSEnvios");
        try {
            conexion = SQLServerDAOFactory.conecta();
            PreparedStatement stmt = conexion.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                sms = instanciarSMS(rs);
                listaSMS.add(sms);
            }
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(SQLServerAlumnoDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            SQLServerDAOFactory.desconecta(conexion);
        }
        
        return listaSMS;
    }

    /**
     * Metodo que crea una nueva instancia de un SMSEnvio.
     * Autor: Xarles y Borja
     * @param rs: Contiene los datos del nuevo SMSEnvio.
     * @return Devuelve un objeto SMSEnvio con los datos establecidos.
     */
    public SMSEnvio instanciarSMS(ResultSet rs) {
        SMSEnvio sms = new SMSEnvio();

        try {
            sms.setIdCandidato(rs.getInt("IDCandidato"));
            sms.setSmsMenId(rs.getInt("SMSMenId"));
            sms.setIdConvocatoria(rs.getInt("IDConvocatoria"));
            sms.setIdRespuesta(rs.getInt("IDRespuesta"));
            sms.setFechaHora(rs.getTimestamp("FechaHora"));
            sms.setMovil(rs.getInt("Movil"));
            sms.setMensaje(rs.getString("Mensaje"));
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        } finally {
            
        }

        return sms;
    }

    /*
     * Metodo que modifica los datos de un alumno.
     * @param alumno: Datos de un alumno.
     * @return Devuelve el identificador del alumno que hemos modificado.
     */
    public int modificarAlumno(Alumno alumno) throws GestorFormacionExcepcion {
        Connection con = null;
        SentenciaNula stmt = null;
        ResultSet rs = null;

        try {
            con = SQLServerDAOFactory.conecta();
        } catch (SQLException ex) {
            Logger.getLogger(SQLServerAlumnoDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-conexion"));
        }

        try {
            PreparedStatement sql = con.prepareStatement("SELECT  AluNIF FROM Alumno WHERE UPPER(AluNIF)=? AND AluId<>?");
            sql.setString(1, alumno.getAluNIF().toUpperCase());
            sql.setInt(2, alumno.getAluId());
            rs=sql.executeQuery();

            if(!rs.next()){
                stmt = new SentenciaNula(con, "UPDATE Alumno SET aluNIF=?, aluNSS=?, aluTlf1=?, aluTlf2=?, aluMail=?, aluDir=?, aluLocalidad=?, aluFechNac=?, aluNom=?, aluApe1=?,  aluApe2=?, aluObserv=?, empId=?, proId=? WHERE aluId=" + alumno.getAluId());
                stmt.setString(1, alumno.getAluNIF());
                stmt.setString(2, alumno.getAluNSS());
                stmt.setString(3, alumno.getAluTlf1());
                stmt.setString(4, alumno.getAluTlf2());
                stmt.setString(5, alumno.getAluMail());
                stmt.setString(6, alumno.getAluDir());
                stmt.setString(7, alumno.getAluLocalidad());
                stmt.setTimestamp(8, alumno.getAluFechNac());
                stmt.setString(9, alumno.getAluNom());
                stmt.setString(10, alumno.getAluApe1());
                stmt.setString(11, alumno.getAluApe2());
                stmt.setString(12, alumno.getAluObserv());
                stmt.setInt(13, alumno.getEmpId());
                stmt.setInt(14, alumno.getProId());
                stmt.executeUpdate();
                stmt.close();
            }else{
                 throw new GestorFormacionExcepcion(rb.getString("existe-nif-modificar"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(SQLServerAlumnoDAO.class.getName()).log(Level.SEVERE, null, ex);
            throw new GestorFormacionExcepcion(rb.getString("no-sql"));
        } finally {
            SQLServerDAOFactory.desconecta(con);            
        }

        return alumno.getAluId();
    }

  
}